package org.usts.trainning.supermarket.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import java.util.Properties;

public class DBUtilities {
	public static Connection connect(String DriverName, String URL, String UserName, String PassWord) {
		Connection conn = null;
		try {
			Class.forName(DriverName).newInstance();
			// MySQL5.0 的用法
			conn = DriverManager.getConnection(URL, UserName, PassWord);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * * 从配置文件中获取数据库配置，并获得数据库连接对象
	 * 
	 * @param configFileName
	 *            配置文件名（含路径）
	 * @return Connection 对象
	 */
	public static Connection connect(String configFileName) {
		Connection conn = null;
		String URL = null;
		Properties prop = new Properties();
		try {
			prop.load(new FileInputStream(configFileName));
			String driver = prop.getProperty("driver");
			URL = prop.getProperty("url");
			String user = prop.getProperty("user");
			String password = prop.getProperty("password");

			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(URL, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * * 从配置文件中读入数据库配置信息,生成 URL
	 * 
	 * @param configFileName
	 *            配置文件名（含路径）
	 * @return URL
	 */
	public static String getURL(String configFileName) {
		String URL = null;
		Properties prop = new Properties();
		try {
			prop.load(new FileInputStream(configFileName));
			String driver = prop.getProperty("driver");
			URL = prop.getProperty("URL");
			String user = prop.getProperty("user");
			String password = prop.getProperty("password");
			// URL = "jdbc:" + "mysql" + "://" + ip + ":" + port + "/" + defaultDB +
			// "?localhost:3306/" + dbname + "test?user="
			// + username + "&password=" + passwd +
			// "&useUnicode=true&characterEncoding=utf-8";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return URL;
	}

	/**
	 * * 执行一个查询 * @param conn Connection 对象
	 * 
	 * @param strSQL
	 *            执行的 SQL语句
	 * @return 查询结果 ResultSet 对象
	 */
	public static ResultSet querySQL(Connection conn, String strSQL) {
		ResultSet rs = null;
		try {
			Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery(strSQL);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 执行 Create,Insert,Update,Delete 语句
	 * 
	 * @param conn
	 *            Connection 对象
	 * @param UpdateSQL
	 * @return 成功 true, 失败 false.
	 */
	public static boolean updateSQL(Connection conn, String UpdateSQL) {
		boolean result = false;
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(UpdateSQL);
			result = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 执行 SQL 查询
	 * 
	 * @param conn
	 *            Connection 对象
	 * @param strSQL
	 *            执行的 SQL 语句
	 * @param params
	 *            参数数组
	 * @return 查询结果 ResultSet 对象（记录集）
	 */
	public static ResultSet querySQL(Connection conn, String strSQL, String[] params) {
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			// 1.创建 SQL 语句对象：
			pst = conn.prepareStatement(strSQL);
			for (int i = 1; i <= params.length; i++) {
				// pst.setString(i, params[i - 1]);
				pst.setInt(i, Integer.parseInt(params[i - 1]));
			}
			// 2.执行 SQL 语句
			rs = pst.executeQuery();
		} catch (IndexOutOfBoundsException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs; // 3.返回执行结果.
	}

	public static ResultSet querySQL_2(Connection conn, String strSQL, String[] params) {
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			// 1.创建 SQL 语句对象：
			pst = conn.prepareStatement(strSQL);
			for (int i = 1; i <= params.length; i++) {
				pst.setString(i, params[i - 1]);
				// pst.setInt(i, Integer.parseInt(params[i - 1]));
			}
			// 2.执行 SQL 语句
			rs = pst.executeQuery();
		} catch (IndexOutOfBoundsException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs; // 3.返回执行结果.
	}

	/**
	 * 执行 Create,Insert,Update,Delete 语句
	 * 
	 * @param conn
	 *            Connection 对象
	 * @param updateSQL
	 *            执行的 SQL 语句
	 * @param params
	 *            参数(数组)
	 * @return 成功 true, 失败 false.
	 */
	public static boolean updateSQL(Connection conn, String updateSQL, String[] params) {
		boolean result = false;
		// 2.创建 SQL 语句对象：
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(updateSQL);
			for (int i = 1; i <= params.length; i++) {
				pst.setString(i, params[i - 1]);
			}
			// 3.执行 SQL 语句
			int count = pst.executeUpdate();
			// 4.处理执行结果，并返回
			if (count >= 1) {
				result = true;
			}
		} catch (IndexOutOfBoundsException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}

}
